package com.vanke.agent.common;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;

import com.vanke.agent.ADataSource;
import com.vanke.agent.C;
import com.vanke.agent.bean.FollowRecord;
import com.vanke.agent.bean.FollowRecordMessage;
import com.vanke.agent.bean.NoticeBean;
import com.vanke.agent.sync.AgentContext;

public class FollowRecordUtil {
	
	static Logger logger = Logger.getLogger(FollowRecordUtil.class);
	

	/*
	 * 加载指定客户的，不包含预约的跟进记录
	 */
	public static List<FollowRecord> loadFollowRecord(Connection con,
	        long cstguid, String code, Logger log) {
		List<FollowRecord> list = new ArrayList<FollowRecord>();
		if (cstguid == 0) {
			return list;
		}
		Statement stmt = null;
		ResultSet rs = null;
		String table = CstUtil.retrieveCstReceiveTable(cstguid);
		String sql = "select "   //
		        + "CstGUID,"     //
		        + "OriCstGUID,"  //
		        + "ProjGUID,"    //
		        + "ProjCode,"    //
		        + "ReceiveType," //
		        + "followType,"  //
		        + "cstType,"     //
		        + "ReceiveDate," //
		        + "Total,"       //
		        + "TradeGUID,"   //
		        + "`Comment`,"   //
		        + "RoomGUID,"   //
		        + "Room,"   //
		        + "Huxing"   //
		        + " from " + table + //
		        " where cstguid=" + cstguid + " and appointType=0";
		try {
			stmt = con.createStatement();
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				FollowRecord fr = new FollowRecord();
				int col = 1;
				fr.setCstGUID(rs.getLong(col++));
				fr.setOriCstGUID(rs.getString(col++));
				fr.setProjGUID(rs.getString(col++));
				fr.setProjCode(rs.getString(col++));
				fr.setReceiveType(rs.getString(col++));
				fr.setFollowType(rs.getInt(col++));
				fr.setCstType(rs.getInt(col++));
				fr.setReceiveDate(rs.getTimestamp(col++));
				fr.setTotal(rs.getDouble(col++));
				fr.setTradeGUID(rs.getString(col++));
				fr.setComment(rs.getString(col++));
				fr.setRoomGUID(rs.getString(col++));
				fr.setRoom(rs.getString(col++));
				fr.setHuxing(rs.getString(col++));
				list.add(fr);
			} // end while(rs.next())
		} catch (Exception e) {
			e.printStackTrace();
			log.error(code + "加载跟进记录异常：" + e);
		} finally {
			ADataSource.close(rs, stmt, null);
		}
		log.info(code + "暂存跟进记录[" + cstguid + "]\n" + list);
		return list;
	} // ~loadFollowRecord

	public static String getMessageType(int followType) {
		switch (followType) {
		case 7:
			return C.NOTICE_GETIN;
		case 6:
			return C.NOTICE_CONTRACT;
		case 5:
			return C.NOTICE_ORDER;
		case 4:
			return C.NOTICE_BOOKING;
		default:
			return C.NOTICE_FOLLOW;
		}
	} // ~

	/* 重推跟进通知 */
	public static void resendFollowRecords(List<FollowRecord> frList,
	        long retainGuid) {
		for (FollowRecord fr : frList) {
			int followType = fr.getFollowType();
			String msgType = getMessageType(followType);
			int state = -1;
			if (followType == C.FOLLOW_ORDER || followType == C.FOLLOW_CONTRACT) {
				state = C.TRADE_STATUS_NEW;
			}
			NoticeBean bean = new NoticeBean();
			bean.setMsgType(msgType);
			bean.setCstGuid(retainGuid);
			bean.setProjCode(fr.getProjCode());
			bean.setTime(fr.getReceiveDate());
			bean.setTotal(fr.getTotal());
			bean.setFollowType(fr.getFollowType());
			bean.setContent(fr.getComment());
			bean.setState(state);
			bean.setRoomGUID(fr.getRoomGUID());
			bean.setRoom(fr.getRoom());
			bean.setHuxing(fr.getHuxing());
			TradeUtil.sendNotice(bean);
			// TradeUtil.sendNotice(msgType, // 消息类型
			// retainGuid, // 客户GUID
			// fr.getProjCode(), // 项目编号
			// fr.getReceiveDate(), // 跟进时间
			// fr.getTotal(), // 总额
			// fr.getFollowType(), // 跟进类型
			// fr.getComment(), // 备注说明
			// state, //
			// fr.getRoomGUID(),
			// fr.getRoom(),
			// fr.getHuxing()
			// ); //
		} // end for
	} // ~resendFollowRecords

	public static int getFollowType(String recvType) {
		recvType = recvType == null ? "" : recvType.trim();
		int followType = 0;
		if (recvType.equals("来访") || recvType.equals("上门")) {
			followType = C.FOLLOW_VISIT; // 3
		} else if (recvType.equals("去电") || recvType.equals("来电")) {
			followType = C.FOLLOW_CALL;	// 2
		} else if (recvType.equals("其它") || recvType.equals("其他")) {
			followType = C.FOLLOW_OTHER; // 8
		} else if (recvType.equals("转机会")) {
			followType = C.FOLLOW_TO_OPP; // 9
		}
		return followType;
	}

	/**
	 * 查询明源跟进
	 * @param cstGUID
	 * @return
	 */
	public static List<FollowRecord> retrieveFollowRecord(String oriGUID) {
		List<FollowRecord> list = new ArrayList<FollowRecord>();
		Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = myCon.createStatement();
			
			String sql = "SELECT cr.Description,cr.ReceiveType,cr.ReceiveDate,so.ProjGUID FROM s_CstReceive as cr left join s_Opportunity as so on cr.OppGUID = so.OppGUID where cr.CstGUID in ("+oriGUID+") and DATEDIFF(dd, cr.CreatonDate, GETDATE()) = 0 " + " order by cr.CreatonDate asc";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				FollowRecord fr = new FollowRecord();
				fr.setFollowContent(rs.getString(1));
				String receiveType = rs.getString(2);
				fr.setFollowType(getFollowType(receiveType));
				fr.setReceiveDate(rs.getTimestamp(3));
				fr.setReceiveType(receiveType);
				String projGUID = rs.getString(4);
				fr.setProjCode(ProjectUtil.getProjectCode(myCon, projGUID));
				list.add(fr);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, stmt, myCon);
		}
		return list;
	}
	
	
	/**
	 * 查询明源认筹
	 * @param cstGUID
	 * @return
	 */
	public static List<FollowRecord> retrieveBookingRecord(String oriGUID) {
		List<FollowRecord> list = new ArrayList<FollowRecord>();
		Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = myCon.createStatement();
			String sql = "select b.CreatedOn,b.ProjGUID from s_Booking2Cst bc LEFT JOIN s_Booking b on bc.BookingGUID = b.BookingGUID where bc.CstGUID in ("+oriGUID+") and DATEDIFF(dd, b.CreatedOn, GETDATE()) = 0 ";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				FollowRecord fr = new FollowRecord();
				fr.setReceiveDate(rs.getTimestamp(1));
				String projGUID = rs.getString(2);
				fr.setProjCode(ProjectUtil.getProjectCode(myCon, projGUID));
				fr.setFollowType(C.FOLLOW_BOOKING);
				fr.setFollowContent("认筹");
				fr.setReceiveType("认筹");
				list.add(fr);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, stmt, myCon);
		}
		return list;
	}
	
	
	
	/**
	 * 查询明源认购
	 * @param cstGUID
	 * @return
	 */
	public static List<FollowRecord> retrieveOrderRecord(String oriGUID) {
		List<FollowRecord> list = new ArrayList<FollowRecord>();
		Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = myCon.createStatement();
			String sql = "select o.ModiDate,o.ProjGUID from s_Trade2Cst tc LEFT JOIN  s_Order o on tc.TradeGUID = o.TradeGUID where tc.CstGUID in ("+oriGUID+") and DATEDIFF(dd, o.ModiDate, GETDATE()) = 0 and o.status = '激活' ";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				FollowRecord fr = new FollowRecord();
				fr.setReceiveDate(rs.getTimestamp(1));
				String projGUID = rs.getString(2);
				fr.setProjCode(ProjectUtil.getProjectCode(myCon, projGUID));
				fr.setFollowType(C.FOLLOW_ORDER);
				fr.setFollowContent("认购");
				fr.setReceiveType("认购");
				list.add(fr);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, stmt, myCon);
		}
		return list;
	}
	
	/**
	 * 查询明源签约
	 * @param cstGUID
	 * @return
	 */
	public static List<FollowRecord> retrieveContractRecord(String oriGUID) {
		List<FollowRecord> list = new ArrayList<FollowRecord>();
		Connection myCon = ADataSource.getConnection(ADataSource.SCHEMA_MY);
		Statement stmt = null;
		ResultSet rs = null;
		try {
			stmt = myCon.createStatement();
			String sql = "select c.ModiDate,c.ProjGUID from s_Trade2Cst tc LEFT JOIN  s_Contract c on tc.TradeGUID = c.TradeGUID where tc.CstGUID in ("+oriGUID+") and DATEDIFF(dd, c.ModiDate, GETDATE()) = 0 and c.status = '激活'";
			rs = stmt.executeQuery(sql);
			while (rs.next()) {
				FollowRecord fr = new FollowRecord();
				fr.setReceiveDate(rs.getTimestamp(1));
				String projGUID = rs.getString(2);
				fr.setProjCode(ProjectUtil.getProjectCode(myCon, projGUID));
				fr.setFollowType(C.FOLLOW_CONTRACT);
				fr.setFollowContent("签约");
				fr.setReceiveType("签约");
				list.add(fr);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			ADataSource.close(rs, stmt, myCon);
		}
		return list;
	}
	
	/**
	 * 将跟进转换为可用的类型
	 * @param frs
	 * @return
	 */
	public static List<FollowRecordMessage> converFollowRecord(List<FollowRecord> frs){
		List<FollowRecordMessage> frMessages = new ArrayList<FollowRecordMessage>();
		for (FollowRecord fr : frs) {
			FollowRecordMessage frm = new FollowRecordMessage();
			frm.setFollowContent(fr.getFollowContent());
			frm.setFollowTime(fr.getReceiveDate().getTime());
			frm.setFollowType(fr.getFollowType());
			frm.setProjCode(fr.getProjCode());
			frm.setReceiveType(fr.getReceiveType());
			frMessages.add(frm);
		}
		return frMessages;
	} 
	
	/**
	 * 根据客户ID查询明源GUID
	 * @return
	 */
	public static String oriGUIDbyCstGUID(long cstGUID){
		String oriGUID = "";
		Set<String> oriGUIDS = loadOriGUIDMapping(cstGUID);
		if (oriGUIDS == null) {
			return oriGUID;
		}
		for (String ori : oriGUIDS) {
			oriGUID += "'"+ori+"',";
		}
		if (oriGUID.length()>1) {
			oriGUID = oriGUID.substring(0,oriGUID.length()-1);
		}
		return oriGUID;
	}
	
	protected static Set<String> loadOriGUIDMapping(long cstGUID) {
		logger.info("loadOriGUIDMapping .. start");
		Set<String> set = new HashSet<String>();
		Connection bizCon = ADataSource.getConnection(ADataSource.SCHEMA_BIZ);
		List<String> tables = CstUtil.loadTables(bizCon, "cst_cstguidmapping_");
		for (String table : tables) {
			Statement stmt = null;
			ResultSet rs = null;
			try {
				stmt = bizCon.createStatement();
				String sql = "select OriGUID,CstGUID from " + table
						+ " where CstGUID=" + cstGUID;
				rs = stmt.executeQuery(sql);
				while (rs.next()) {
					String origuid = rs.getString(1);
					set.add(origuid);
					logger.info("set " + set.toString());
				}
				
			} catch (Exception e) {
				e.printStackTrace();
				AgentContext.exit("加载GUID映射缓存失败");
			} finally {
				ADataSource.close(rs, stmt, null);
			}
		}
		ADataSource.close(bizCon);
		logger.info("loadOriGUIDMapping .. end");
		return set;
	} 
	
//	
//	public static FollowRecord retrieveFollowRecord(Connection myCon,
//	        Connection bizCon, ResultSet rs) {
//		try {
//			String projGUID = null;
//			String projCode = null;
//
//			String oriCstGUID = rs.getString("CstGUID");
//			long uid = CstUtil.getCstGUIDByOriGuid(bizCon, oriCstGUID);
//			if (uid == 0) {
//				uid = CstUtil.retrieveMemship(oriCstGUID);
//			}
//
//			if (uid == 0) { // 反查仍未关联到客户，关联同到的本条记录不同步
//				return null;
//			}
//			// + "CreatonDate," //
//
//			FollowRecord fr = new FollowRecord();
//			fr.setBuGUID(rs.getString("BuGUID"));
//			fr.setDescription(rs.getString("Description"));
//			fr.setOppGUID(rs.getString("OppGUID"));
//			String recvType = rs.getString("ReceiveType");
//			fr.setFollowType(getFollowType(recvType));
//			fr.setReceiveDate(rs.getTimestamp("ReceiveDate"));
//			fr.setUserGUID(rs.getString("UserGUID"));
//			fr.setMyGUID(rs.getString("MyGUID"));
//			fr.setTotal(0);
//			fr.setSequence(rs.getInt("pk_zzcx_id"));
//
//			String sql = "select ProjGUID from s_Opportunity where OppGUID = ?";
//			PreparedStatement proPs = myCon.prepareStatement(sql,
//			        ResultSet.TYPE_SCROLL_INSENSITIVE,
//			        ResultSet.CONCUR_READ_ONLY);
//			proPs.setString(1, rs.getString("OppGUID"));
//			ResultSet proRs = proPs.executeQuery();
//
//			if (proRs.next()) {
//				projGUID = proRs.getString("ProjGUID");
//			}
//			fr.setProjGUID(projGUID);
//			fr.setOriCstGUID(oriCstGUID);
//			projCode = ProjectUtil.getProjectCode(myCon, projGUID);
//			fr.setProjCode(projCode);
//
//			return fr;
//		} catch (Exception e) {
//			logger.error(e.getMessage(), e);
//			return null;
//		}
//	}
} // /~FollowRecordUtil
